package com.jsle.financial.excel;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import com.jsle.financial.domain.DataEntity;

public class ExcelParser {
    public static final String MSG_DUPLICATE = "不能上传重复数据，第%s、%s行重复！";
    public static final String MSG_COLUMNS_NOT_MATCH = "表格中的列数不符合模板，期望%s列，实际%s列！";
    public static final String MSG_BAD_FORMAT = "第%s行数据不符合格式，请选择符合模板格式的文件上传！";
    public static final String MSG_BAD_FILE = "请选择符合模板格式的文件上传！";
    private static final int FIRST_RECORD_ROWNUM = 3; // 第一行数据的行下标
    private static final int FIRST_RECORD_COLNUM = 2; // 第一列数据的下标
    private static final int NUMER_OF_DATA_IN_A_ROW = 3; // 每行有多少对数据
    private XSSFSheet sheet;
    private String msg; // parse失败后的消息
    private Date accdate;

    private List<DataEntity> result = new ArrayList<DataEntity>();

    public List<DataEntity> getResult() {
	return result;
    }

    public void parse(InputStream is) {
	if (is == null) {
	    msg = MSG_BAD_FILE;
	    return;
	}

	XSSFWorkbook workbook = null;
	try {
	    workbook = new XSSFWorkbook(is);
	} catch (Exception e) {
	    e.printStackTrace();
	    msg = MSG_BAD_FILE;
	    return;
	}

	sheet = workbook.getSheetAt(0);
	accdate = sheet.getRow(FIRST_RECORD_ROWNUM).getCell(3)
	        .getDateCellValue();

	int lastRowNum = sheet.getLastRowNum();
	int rowIndex = FIRST_RECORD_ROWNUM;
	while (rowIndex <= lastRowNum) {
	    int lastRowNumInOneType = getRowSpan(rowIndex); // 一个分类数据的最后一行下标
	    parseType(rowIndex, lastRowNumInOneType);
	    rowIndex = lastRowNumInOneType;
	}
    }

    /**
     * 解析一个类型的数据
     * 
     * @param fromRow
     *            数据开始的行下标
     * @param toRow
     *            数据结束的行下标
     */
    private void parseType(int fromRow, int toRow) {
	String type = sheet.getRow(fromRow).getCell(1).getStringCellValue();
	for (int rowNum = fromRow; rowNum < toRow; rowNum++) {
	    parseRow(type, sheet.getRow(rowNum), isFirstType(fromRow));
	}
    }

    private boolean isFirstType(int fromRow) {
	return fromRow == FIRST_RECORD_ROWNUM;
    }

    private void parseRow(String type, XSSFRow row, boolean isFirstType) {
	int i = isFirstType ? 1 : 0; // 如果是第一种类型的数据，要跳过本日日期的解析
	for (; i < NUMER_OF_DATA_IN_A_ROW; i++) {
	    parseData(type, row, FIRST_RECORD_COLNUM + i * 2);
	}
    }

    /**
     * 解析指定列的数据
     * 
     * @param type
     *            所属类型
     * @param row
     * @param col
     *            列下标
     */
    private void parseData(String type, XSSFRow row, int col) {
	String itemName = row.getCell(col).getStringCellValue();
	double itemValue = row.getCell(col + 1).getNumericCellValue();
	System.out.println("--" + itemName + "=" + itemValue);
	if (StringUtils.hasLength(itemName)) {
	    result.add(new DataEntity(accdate, type, itemName, itemValue));
	}
    }

    public int getRowSpan(int startRow) {
	int rownum = startRow;
	XSSFRow row = sheet.getRow(startRow);
	int index = (int) row.getCell(0).getNumericCellValue();
	int index2 = index;
	while (index2 <= index) {
	    if (rownum >= sheet.getLastRowNum()) {
		return sheet.getLastRowNum() + 1;
	    }

	    index2 = (int) sheet.getRow(rownum).getCell(0)
		    .getNumericCellValue();
	    rownum++;
	}
	return rownum - 1;
    }
}
